﻿Imports System.Data
Imports System.Data.Odbc
Imports System.Text
Imports System.Collections.Generic
Imports WSEntidades
Imports WSFactory
Imports MySql.Data.MySqlClient
Imports Utilerias

Public Class UsuarioDAL
    Private cadConex As String = ""

    Public Sub New(ByVal pCadConex As String)
        Me.cadConex = pCadConex
    End Sub
#Region "FuncionesUtilizables"
    Public Function autenticarUsuario(ByVal pUser As String, ByVal pPwd As String,
                                      ByRef boolCambiarPwd As Boolean, ByRef boolUsuarioBloqueado As Boolean,
                                      ByRef dtFechaCambioPwd As DateTime,
                                      ByRef razonSocial As String, ByRef esquemaFacturacion As Enumeraciones.esquemaFacturacion,
                                      ByRef nombreUsuario As String) As Boolean
        razonSocial = ""
        esquemaFacturacion = Enumeraciones.esquemaFacturacion.Indefinido
        Dim strEsquemaFact As String = ""
        Dim boolEsValido As Boolean = False
        Try
            Using myConn As MySqlConnection = New MySqlConnection(Me.cadConex)
                Using myComm As MySqlCommand = myConn.CreateCommand()
                    myComm.CommandText = "spLoginUser; "
                    myComm.CommandType = CommandType.StoredProcedure
                    myComm.Parameters.Clear()
                    myComm.Parameters.AddWithValue("?usuario", pUser)
                    myComm.Parameters.AddWithValue("?contrasenia", RijndaelSimple.encriptaDefault(pPwd))
                    myComm.Parameters.Add(New MySqlParameter("?esValido", MySqlDbType.Bit))
                    myComm.Parameters.Add(New MySqlParameter("?cambiarContrasenia", MySqlDbType.Bit))
                    myComm.Parameters.Add(New MySqlParameter("?bloqueadoIntentosFallidos", MySqlDbType.Bit))
                    myComm.Parameters.Add(New MySqlParameter("?fechaUltimoCambioContrasenia", MySqlDbType.DateTime))
                    myComm.Parameters.Add(New MySqlParameter("?razonSocialEmpr", MySqlDbType.VarChar))
                    myComm.Parameters.Add(New MySqlParameter("?esquemaFacturacion", MySqlDbType.VarChar))
                    myComm.Parameters.Add(New MySqlParameter("?nombUser", MySqlDbType.VarChar))
                    myComm.Parameters("?esValido").Direction = ParameterDirection.Output
                    myComm.Parameters("?cambiarContrasenia").Direction = ParameterDirection.Output
                    myComm.Parameters("?bloqueadoIntentosFallidos").Direction = ParameterDirection.Output
                    myComm.Parameters("?fechaUltimoCambioContrasenia").Direction = ParameterDirection.Output
                    myComm.Parameters("?razonSocialEmpr").Direction = ParameterDirection.Output
                    myComm.Parameters("?esquemaFacturacion").Direction = ParameterDirection.Output
                    myComm.Parameters("?nombUser").Direction = ParameterDirection.Output
                    myConn.Open()
                    myComm.ExecuteNonQuery()
                    boolEsValido = Convert.ToBoolean(myComm.Parameters("?esValido").Value)
                    boolCambiarPwd = Convert.ToBoolean(myComm.Parameters("?cambiarContrasenia").Value)
                    boolUsuarioBloqueado = Convert.ToBoolean(myComm.Parameters("?bloqueadoIntentosFallidos").Value)
                    If (myComm.Parameters("?fechaUltimoCambioContrasenia").Value Is DBNull.Value) Then
                        dtFechaCambioPwd = New DateTime()
                    Else
                        dtFechaCambioPwd = Convert.ToDateTime(myComm.Parameters("?fechaUltimoCambioContrasenia").Value)
                    End If
                    razonSocial = Convert.ToString(myComm.Parameters("?razonSocialEmpr").Value)
                    Select Case Convert.ToString(myComm.Parameters("?esquemaFacturacion").Value)
                        Case "CFDI_3.2"
                            esquemaFacturacion = Enumeraciones.esquemaFacturacion.CFDI_3_2
                    End Select
                    nombreUsuario = Convert.ToString(myComm.Parameters("?nombUser").Value)
                End Using
            End Using
            Return boolEsValido
        Finally
            boolEsValido = Nothing
        End Try
    End Function

    Public Function modificaContraseña(ByVal usuario As String, ByRef strCorreoUsuario As String) As String
        strCorreoUsuario = ""
        Dim strContraseña As String = ""
        Try
            strContraseña = RandomPassword.Generate(8)
            Using myConn As MySqlConnection = New MySqlConnection(Me.cadConex)
                Using myComm As MySqlCommand = myConn.CreateCommand()
                    myComm.CommandText = "UPDATE usuarios " & _
                                            "INNER JOIN perfiles " & _
                                                "ON (usuarios.IdPerfil = perfiles.IdPerfil)" & _
                                            "SET ContrHist2 = ContrHist1, " & _
                                                "ContrHist1 = Contr, " & _
                                                "Contr = ?Contr, " & _
                                                "CambioContr = Now(), " & _
                                                "CambiarContr = 'S' " & _
                                            "WHERE perfiles.Activo ='S' " & _
                                                "AND usuarios.Activo ='S' " & _
                                                "AND  Usuario = ?User;"
                    myComm.CommandType = CommandType.Text
                    myComm.Parameters.Clear()
                    myComm.Parameters.AddWithValue("?Contr", RijndaelSimple.encriptaDefault(strContraseña))
                    myComm.Parameters.AddWithValue("?User", usuario)
                    myConn.Open()
                    If (myComm.ExecuteNonQuery() = 1) Then
                        myComm.CommandText = "SELECT Correo " & _
                                                "    FROM " & _
                                                "        usuarios " & _
                                                "        INNER JOIN perfiles  " & _
                                                "        ON (usuarios.IdPerfil = perfiles.IdPerfil) " & _
                                                "    WHERE perfiles.Activo ='S' " & _
                                                "        AND usuarios.Activo ='S' " & _
                                                "        AND usuarios.Usuario = ?User; "
                        myComm.Parameters.Clear()
                        myComm.Parameters.AddWithValue("?User", usuario)
                        strCorreoUsuario = convertDTFromDb.ConvertString(myComm.ExecuteScalar())
                    Else
                        strContraseña = ""
                        strCorreoUsuario = ""
                    End If
                End Using
            End Using

            Return strContraseña

        Finally
            strContraseña = Nothing
        End Try
    End Function

    Public Function esContraseñaHistorica(ByVal usuario As String, ByVal pwdNuevo As String) As Boolean
        Using myConn As MySqlConnection = New MySqlConnection(Me.cadConex)
            Using myComm As MySqlCommand = myConn.CreateCommand()
                myComm.CommandText = "SELECT COUNT(*) " & _
                                        "FROM usuarios " & _
                                        "WHERE Usuario = ?User " & _
                                            "AND (Contr = ?PwdNvo " & _
                                                "OR ContrHist1 = ?PwdNvo " & _
                                                "OR ContrHist2 = ?PwdNvo)"
                myComm.CommandType = CommandType.Text
                myComm.Parameters.Clear()
                myComm.Parameters.AddWithValue("?User", usuario)
                myComm.Parameters.AddWithValue("?PwdNvo", pwdNuevo)
                myConn.Open()
                If (Convert.ToInt16(myComm.ExecuteScalar()) > 0) Then
                    Return True
                Else
                    Return False
                End If
            End Using
        End Using
    End Function

    Public Function modificaContraseña(ByVal usuario As String, ByVal pwdActual As String, ByVal pwdNuevo As String) As Boolean
        Using myConn As MySqlConnection = New MySqlConnection(Me.cadConex)
            Using myComm As MySqlCommand = myConn.CreateCommand()
                myComm.CommandText = "UPDATE usuarios " & _
                                        "INNER JOIN perfiles " & _
                                            "ON (usuarios.IdPerfil = perfiles.IdPerfil)" & _
                                        "SET ContrHist2 = ContrHist1, " & _
                                            "ContrHist1 = Contr, " & _
                                            "Contr = ?ContrNva, " & _
                                            "CambiarContr = 'N', " & _
                                            "CambioContr = Now() " & _
                                        "WHERE perfiles.Activo ='S' " & _
                                            "AND usuarios.Activo ='S' " & _
                                            "AND Usuario = ?User " & _
                                            "AND Contr = ?ContrAct;"
                myComm.CommandType = CommandType.Text
                myComm.Parameters.Clear()
                myComm.Parameters.AddWithValue("?ContrNva", RijndaelSimple.encriptaDefault(pwdNuevo))
                myComm.Parameters.AddWithValue("?User", usuario)
                myComm.Parameters.AddWithValue("?ContrAct", RijndaelSimple.encriptaDefault(pwdActual))
                myConn.Open()
                If (myComm.ExecuteNonQuery() = 1) Then
                    Return True
                Else
                    Return False
                End If
            End Using
        End Using
    End Function

    Public Function modificaContraseñaPorCorreo(ByVal correo As String, ByRef strUsuario As String) As String
        strUsuario = ""
        Dim strContraseña As String = ""
        Try
            strContraseña = RandomPassword.Generate(8)
            Using myConn As MySqlConnection = New MySqlConnection(Me.cadConex)
                Using myComm As MySqlCommand = myConn.CreateCommand()
                    myComm.CommandText = "UPDATE usuarios " & _
                                            "INNER JOIN perfiles " & _
                                                "ON (usuarios.IdPerfil = perfiles.IdPerfil)" & _
                                            "SET ContrHist2 = ContrHist1, " & _
                                                "ContrHist1 = Contr, " & _
                                                "Contr = ?Contr, " & _
                                                "CambioContr = Now(), " & _
                                                "CambiarContr = 'S' " & _
                                            "WHERE perfiles.Activo ='S' " & _
                                                "AND usuarios.Activo ='S' " & _
                                                "AND  Correo = ?Correo;"
                    myComm.CommandType = CommandType.Text
                    myComm.Parameters.Clear()
                    myComm.Parameters.AddWithValue("?Contr", RijndaelSimple.encriptaDefault(strContraseña))
                    myComm.Parameters.AddWithValue("?Correo", correo)
                    myConn.Open()
                    If (myComm.ExecuteNonQuery() = 1) Then
                        myComm.CommandText = "SELECT Usuario " & _
                                                "    FROM " & _
                                                "        usuarios " & _
                                                "        INNER JOIN perfiles  " & _
                                                "        ON (usuarios.IdPerfil = perfiles.IdPerfil) " & _
                                                "    WHERE perfiles.Activo ='S' " & _
                                                "        AND usuarios.Activo ='S' " & _
                                                "        AND usuarios.Correo = ?Correo; "
                        myComm.Parameters.Clear()
                        myComm.Parameters.AddWithValue("?Correo", correo)
                        strUsuario = convertDTFromDb.ConvertString(myComm.ExecuteScalar())
                    Else
                        strContraseña = ""
                        strUsuario = ""
                    End If
                End Using
            End Using

            Return strContraseña

        Finally
            strContraseña = Nothing
        End Try
    End Function
#End Region



    Public Function registraUsuario(ByVal pUsuario As usuarioBE) As Boolean
        Dim instrSql As String
        Dim oInsParm(0 To 10) As OdbcParameter

        Try

            instrSql = "INSERT INTO ""Usuario""(""Activo"", ""Clave"", ""CorreoElect"", ""IdGrupo"", ""IdUsuario"",""NoIntentos"", ""Nombre"", ""NombreCompleto"", ""PregSecret"", ""RespSecreta"", ""FechaAct"") " & _
                                        "VALUES (?,?,?,?,?,?,?,?);"

            oInsParm(0) = New OdbcParameter("", pUsuario.Activo)
            oInsParm(1) = New OdbcParameter("", pUsuario.Clave)
            oInsParm(2) = New OdbcParameter("", pUsuario.CorreoElect)
            oInsParm(3) = New OdbcParameter("", pUsuario.IdGrupo)
            oInsParm(4) = New OdbcParameter("", pUsuario.IdUsuario)
            oInsParm(5) = New OdbcParameter("", pUsuario.NoIntentos)
            oInsParm(6) = New OdbcParameter("", pUsuario.Nombre)
            oInsParm(7) = New OdbcParameter("", pUsuario.NombreCompleto)
            oInsParm(8) = New OdbcParameter("", pUsuario.PregSecret)
            oInsParm(9) = New OdbcParameter("", pUsuario.RespSecreta)
            oInsParm(10) = New OdbcParameter("", pUsuario.FechaAct)

            'Crea el objeto connection
            Using myConn As OdbcConnection = New OdbcConnection(My.Settings.cs)
                Using myComm As New OdbcCommand(instrSql, myConn)
                    myComm.CommandType = CommandType.Text
                    myComm.Parameters.AddRange(oInsParm)
                    myConn.Open()
                    myComm.ExecuteNonQuery()
                    If (myComm.ExecuteNonQuery() > 0) Then
                        Return True
                    Else
                        Return False
                    End If
                End Using
            End Using
        Catch ex As Exception
            Throw
        Finally
            instrSql = Nothing
            oInsParm = Nothing
        End Try
    End Function

    Public Function modificaUsuario(ByVal pUsuario As usuarioBE) As Boolean
        Dim instrSql As String
        Dim oInsParm(0 To 10) As OdbcParameter
        Try
            instrSql = "UPDATE ""usuario"" " & _
                                        "SET ""Activo""=?, ""Clave""=?, ""CorreoElect""=?, ""FechaAct""=?,""IdGrupo""=?, ""NoIntentos""=?, ""Nombre""=?, ""NombreCompleto""=?, ""PregSecret""=?, ""RespSecreta""=? " & _
                                                "WHERE ""IdUsuario"" = ?;"

            oInsParm(0) = New OdbcParameter("", pUsuario.Activo)
            oInsParm(1) = New OdbcParameter("", pUsuario.Clave)
            oInsParm(2) = New OdbcParameter("", pUsuario.CorreoElect)
            oInsParm(3) = New OdbcParameter("", pUsuario.FechaAct)
            oInsParm(4) = New OdbcParameter("", pUsuario.IdGrupo)
            oInsParm(5) = New OdbcParameter("", pUsuario.NoIntentos)
            oInsParm(6) = New OdbcParameter("", pUsuario.Nombre)
            oInsParm(7) = New OdbcParameter("", pUsuario.NombreCompleto)
            oInsParm(8) = New OdbcParameter("", pUsuario.PregSecret)
            oInsParm(9) = New OdbcParameter("", pUsuario.RespSecreta)
            oInsParm(10) = New OdbcParameter("", pUsuario.IdUsuario)

            'Crea el objeto connection
            Using myConn As OdbcConnection = New OdbcConnection(My.Settings.cs)
                Using myComm As New OdbcCommand(instrSql, myConn)
                    myComm.CommandType = CommandType.Text
                    myComm.Parameters.AddRange(oInsParm)
                    myConn.Open()
                    myComm.ExecuteNonQuery()
                    If (myComm.ExecuteNonQuery() > 0) Then
                        Return True
                    Else
                        Return False
                    End If
                End Using
            End Using

        Catch ex As Exception
            Throw
        Finally
            instrSql = Nothing
            oInsParm = Nothing
        End Try
    End Function

    Public Function getUsuarioById(ByVal pIdUsuario As Integer, ByVal pStrUsuario As String) As List(Of usuarioBE)
        Dim UsuarioLs As List(Of usuarioBE) = New List(Of usuarioBE)
        Dim instrSql As String

        Try
            instrSql = "SELECT ""Activo"", ""Clave"", ""CorreoElect"", ""FechaAct"", ""IdGrupo"",""NoIntentos"", ""Nombre"", ""NombreCompleto"", ""PregSecret"", ""RespSecreta"", ""IdUsuario"" " & _
                                     "FROM ""Usuario"" " & _
                                    "WHERE ""IdUsuario"" = ? ;"

            Using myConn As OdbcConnection = New OdbcConnection(My.Settings.cs)
                Using myComm As OdbcCommand = New OdbcCommand(instrSql, myConn)
                    myComm.CommandType = CommandType.Text
                    myComm.Parameters.Add(New OdbcParameter("", pIdUsuario))
                    myConn.Open()
                    Using myDR As OdbcDataReader = myComm.ExecuteReader()
                        While myDR.Read()
                            Dim usuario As usuarioBE = usuarioFact.Build(myDR)
                            If (Not usuario Is Nothing) Then
                                UsuarioLs.Add(usuario)
                            End If
                            usuario = Nothing
                        End While
                    End Using
                End Using
            End Using
            Return UsuarioLs
        Catch ex As Exception
            Throw
        Finally
            UsuarioLs = Nothing
            instrSql = Nothing
        End Try
    End Function

    Public Function getUsuario(ByVal pIdUsuario As Integer) As usuarioBE
        Dim instrSql As String
        Dim tmpUsuario As usuarioBE
        Try
            instrSql = "SELECT ""Activo"", ""Clave"", ""CorreoElect"", ""FechaAct"", ""IdGrupo"",""NoIntentos"", ""Nombre"", ""NombreCompleto"", ""PregSecret"", ""RespSecreta"", ""IdUsuario"" " & _
                                    "FROM ""Usuario"" " & _
                                    "WHERE ""IdUsuario"" = ? ;"

            Using myConn As OdbcConnection = New OdbcConnection(My.Settings.cs)
                Using myComm As New OdbcCommand(instrSql, myConn)
                    myComm.CommandType = CommandType.Text
                    myComm.Parameters.Add(New OdbcParameter("", pIdUsuario))
                    myConn.Open()
                    Using myDR As OdbcDataReader = myComm.ExecuteReader()
                        If (myDR.HasRows()) Then
                            myDR.Read()
                            tmpUsuario = usuarioFact.Build(myDR)
                        Else
                            tmpUsuario = Nothing
                        End If
                    End Using
                End Using
            End Using
            Return tmpUsuario

        Catch ex As Exception
            Throw
        Finally
            tmpUsuario = Nothing
            instrSql = ""
        End Try
    End Function

    Public Function getUsuarioByName(ByVal pUsuario As String) As usuarioBE
        Dim instrSql As String
        Dim tmpUsuario As usuarioBE
        Try
            instrSql = "SELECT ""Activo"", ""Clave"", ""CorreoElect"", ""FechaAct"", ""IdGrupo"",""NoIntentos"", ""Nombre"", ""NombreCompleto"", ""PregSecret"", ""RespSecreta"", ""IdUsuario"" " & _
                                    "FROM ""Usuario"" " & _
                                    "WHERE ""Nombre"" = ? ;"

            Using myConn As OdbcConnection = New OdbcConnection(My.Settings.cs)
                Using myComm As New OdbcCommand(instrSql, myConn)
                    myComm.CommandType = CommandType.Text
                    myComm.Parameters.Add(New OdbcParameter("", pUsuario))
                    myConn.Open()
                    Using myDR As OdbcDataReader = myComm.ExecuteReader()
                        If (myDR.HasRows()) Then
                            myDR.Read()
                            tmpUsuario = usuarioFact.Build(myDR)
                        Else
                            tmpUsuario = Nothing
                        End If
                    End Using
                End Using
            End Using
            Return tmpUsuario

        Catch ex As Exception
            Throw
        Finally
            tmpUsuario = Nothing
            instrSql = ""
        End Try
    End Function

    Public Function existeUsuario(ByVal pIdUsuario As Integer) As Boolean
        Dim existe As Boolean = False
        Dim instrSql As String
        Dim oSelParm(0 To 0) As OdbcParameter
        Try

            instrSql = "SELECT COUNT(*) " & _
                                    "FROM ""Usuario"" " & _
                                    "WHERE ""IdUsuario"" = ?;"

            oSelParm(0) = New OdbcParameter("", pIdUsuario)
            Dim count As Object = db.ExecScalar(instrSql, CommandType.Text, oSelParm, My.Settings.cs)
            If (Not count Is Nothing) Then
                If (CShort(count) > 0) Then
                    existe = True
                Else
                    existe = False
                End If
            Else
                existe = False
            End If
            Return existe

        Catch ex As Exception
            Throw
        Finally
            oSelParm = Nothing
            instrSql = Nothing
            existe = Nothing
        End Try
    End Function

    Public Function modificaNoIntentos(ByVal numero As Integer, ByVal idUsuario As Integer) As Boolean
        Dim instrSql As String
        Dim oInsParm(0 To 1) As OdbcParameter
        Try
            instrSql = "UPDATE ""usuario"" " & _
                                        "SET ""NoIntentos""=? " & _
                                                "WHERE ""IdUsuario"" = ?;"

            oInsParm(0) = New OdbcParameter("", numero)
            oInsParm(1) = New OdbcParameter("", idUsuario)

            'Crea el objeto connection
            Using myConn As OdbcConnection = New OdbcConnection(My.Settings.cs)
                Using myComm As New OdbcCommand(instrSql, myConn)
                    myComm.CommandType = CommandType.Text
                    myComm.Parameters.AddRange(oInsParm)
                    myConn.Open()
                    myComm.ExecuteNonQuery()
                    If (myComm.ExecuteNonQuery() > 0) Then
                        Return True
                    Else
                        Return False
                    End If
                End Using
            End Using

        Catch ex As Exception
            Throw
        Finally
            instrSql = Nothing
            oInsParm = Nothing
        End Try
    End Function

    Public Function modificaPass(ByVal pass As String, ByVal idUsuario As Integer) As Boolean
        Dim instrSql As String
        Dim oInsParm(0 To 1) As OdbcParameter
        Try
            instrSql = "UPDATE ""usuario"" " & _
                                        "SET ""Clave""=? " & _
                                                "WHERE ""IdUsuario"" = ?;"

            oInsParm(0) = New OdbcParameter("", pass)
            oInsParm(1) = New OdbcParameter("", idUsuario)

            'Crea el objeto connection
            Using myConn As OdbcConnection = New OdbcConnection(My.Settings.cs)
                Using myComm As New OdbcCommand(instrSql, myConn)
                    myComm.CommandType = CommandType.Text
                    myComm.Parameters.AddRange(oInsParm)
                    myConn.Open()
                    myComm.ExecuteNonQuery()
                    If (myComm.ExecuteNonQuery() > 0) Then
                        Return True
                    Else
                        Return False
                    End If
                End Using
            End Using

        Catch ex As Exception
            Throw
        Finally
            instrSql = Nothing
            oInsParm = Nothing
        End Try
    End Function
End Class
